package jdbc;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * Created with IntelliJ IDEA
 * Description:学习批量执行sql语句
 * User:22954
 * Date:2024-05-20
 * Time:11:42
 */
public class Batch {
    /*
    void addBatch()  添加sql语句到批量包中
    int[] executeBatch()  执行批量包中的sql语句
    void clearBatch()  清空批量包中的sql语句
    使用批量包需修改配置的url语句    ...database + ?rewriteBatchedStatements=true
     */
    static Connection connection;

    public static void main(String[] args) throws IOException, SQLException {
        connection = JDBCUtils.getConnection();
//        normalInsert();
        batchInsert();
    }

    private static void normalInsert() throws SQLException {
        String sql = "insert into `admin` values(?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            preparedStatement.setString(1, "jack" + i);
            preparedStatement.setString(2, "666");
            preparedStatement.executeUpdate();
        }
        long end = System.currentTimeMillis();
        System.out.println("cost time:" + (end - start));//cost time:10182
        JDBCUtils.close(null, preparedStatement, connection);
    }

    private static void batchInsert() throws SQLException {
        String sql = "insert into `admin` values(?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            preparedStatement.setString(1, "jack" + i);
            preparedStatement.setString(2, "999");
            preparedStatement.addBatch();
            if ((i + 1) % 1000 == 0) {
                preparedStatement.executeBatch();
                preparedStatement.clearBatch();
            }
        }
        long end = System.currentTimeMillis();
        System.out.println("cost time:" + (end - start));//cost time:145
        JDBCUtils.close(null,preparedStatement,connection);
    }
}
